/*
BEGIN TRANSACTION
EXECUTE sp_save_die_bank_binning_info_wafers 1666, 1033, 3, 3, '100;0;0;', 696, 0.13, 1
select * from informative_binning where lot_id = 1666
ROLLBACK
*/
ALTER PROC sp_save_die_bank_binning_info_wafers
@LOT_ID INT,
@PROCESS_STEP_ID INT,
@GOOD_BINS_COUNT INT,
@WAFER_ID INT,
@BIN_QUANTITIES VARCHAR(200),
@BIN_REJECTED INT,
@YIELD FLOAT,
@HOLD INT

AS
BEGIN
	DECLARE @TMP_IDX INT
	DECLARE @QUERY_STRING NVARCHAR(1000)
	DECLARE @PARAM_STRING NVARCHAR(500)
	DECLARE @BINS_QUANTITY_QUERY_STRING VARCHAR(1000)
	DECLARE @BINS_QUANTITY_FOR_INSERT VARCHAR(1000)
	DECLARE @GOOD_DICE BIGINT
	DECLARE @MRB_LIMIT BIT
	DECLARE @RMA_LIMIT BIT
	DECLARE @OLD_MRB_LIMIT_STATUS BIT		--Old MRB Limit
	DECLARE @OLD_RMA_LIMIT_STATUS BIT		--Old MRB Limit
	DECLARE @CURRENT_BIN_VALUE VARCHAR(20)
	DECLARE @MRB_LIMIT_VALUE BIGINT
	DECLARE @RMA_LIMIT_VALUE BIGINT
	DECLARE @COUNT INT
	DECLARE @PREVIOUS_QTY_str  NVARCHAR(1000)
	DECLARE @PREVIOUS_QTY_QUERY NVARCHAR(1000)
	DECLARE @PREVIOUS_QTY_INT INT
	DECLARE @ParamDefinition NVARCHAR(500)

	DECLARE @BIN_INFO VARCHAR(500)
	DECLARE @IB_BIN_QUANTITIES VARCHAR(1000)
	DECLARE @LOOP_VAR INT
	DECLARE @SQL_QUERY NVARCHAR(1000)
	DECLARE @DIE_BANK_BIN_INFO_UPDATE BIT
	DECLARE @OLD_BIN_QUANTITIES VARCHAR(500)
	DECLARE @BIN_QUANTITIES_COPY VARCHAR(200)
	

	SELECT @COUNT = COUNT(*) FROM INFORMATIVE_BINNING WHERE Lot_ID = @LOT_ID AND Wafer_ID = @WAFER_ID
	-- Update the INFORMATIVE_BINNING table if a wafer with the same wafer id is present in the lot else insert a new record
	IF ( @COUNT > 0 )
	BEGIN

		SELECT @OLD_MRB_LIMIT_STATUS = MRB_LIMIT, @OLD_RMA_LIMIT_STATUS = RMA_LIMIT FROM INFORMATIVE_BINNING WHERE Lot_ID = @LOT_ID AND Wafer_ID = @WAFER_ID

		--PRINT '@OLD_RMA_LIMIT_STATUS  '+ CONVERT ( char, @OLD_RMA_LIMIT_STATUS )
		
		-- Update the wafer details if the wafer is not present in ENGINEERING_LOT and Assembly Release not yet generated.
		IF( ( (SELECT COUNT(*) FROM ENGINEERING_LOTS  WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID) = 0 ) AND
		    (SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE ASLT, ASSM_RELEASE_TABLE ART WHERE ART.ASSEMBLY_RELEASE_ID = ASLT.ASSEMBLY_RELEASE_ID AND ART.STATUS <> 'CANCELLED' AND ASLT.LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID) = 0  )	
		BEGIN
			SET @QUERY_STRING = 'UPDATE INFORMATIVE_BINNING SET GOOD_DICE = @GOOD_DICE, YIELD = @YIELD, BIN_REJECTED = @BIN_REJECTED, MRB_LIMIT = @MRB_LIMIT, RMA_LIMIT = @RMA_LIMIT, DIE_BANK_BIN_INFO_UPDATE = @DIE_BANK_BIN_INFO_UPDATE, ' 
			SET @TMP_IDX = 1
			SET @BINS_QUANTITY_QUERY_STRING = ''
			SET @PREVIOUS_QTY_STR = ''
			SET @OLD_BIN_QUANTITIES = ''
			SET @GOOD_DICE = 0
			SET @MRB_LIMIT = 1
			SET @RMA_LIMIT = 1
			SET @DIE_BANK_BIN_INFO_UPDATE = 1
			SET @BIN_QUANTITIES_COPY = @BIN_QUANTITIES
			WHILE( CHARINDEX( ';', @BIN_QUANTITIES, 0 ) > 0 )
			BEGIN
				SET @CURRENT_BIN_VALUE = SUBSTRING( @BIN_QUANTITIES, 0, CHARINDEX( ';', @BIN_QUANTITIES, 0 ))
				SET @CURRENT_BIN_VALUE = LTRIM(RTRIM( @CURRENT_BIN_VALUE))
				SET @GOOD_DICE = @GOOD_DICE + CAST( @CURRENT_BIN_VALUE AS INT )
				SET @BINS_QUANTITY_QUERY_STRING = @BINS_QUANTITY_QUERY_STRING + 'Bin_' + CONVERT(VARCHAR,@TMP_IDX) + '=' + @CURRENT_BIN_VALUE +','
				SET @OLD_BIN_QUANTITIES = @OLD_BIN_QUANTITIES + 'CONVERT( VARCHAR, ISNULL(BIN_'+ CONVERT(VARCHAR, @TMP_IDX) +',0))+'';''+'
				SET @PREVIOUS_QTY_STR = @PREVIOUS_QTY_STR  + 'Bin_' + CONVERT(VARCHAR,@TMP_IDX) + ' + '
				SET @BIN_QUANTITIES = SUBSTRING( @BIN_QUANTITIES, CHARINDEX(';', @BIN_QUANTITIES, 0)+1, LEN(@BIN_QUANTITIES))
				SET @TMP_IDX = @TMP_IDX + 1
			END
			SET @BINS_QUANTITY_QUERY_STRING = SUBSTRING(@BINS_QUANTITY_QUERY_STRING,0, LEN(@BINS_QUANTITY_QUERY_STRING))
			SET @QUERY_STRING = @QUERY_STRING + @BINS_QUANTITY_QUERY_STRING +' WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID'
			--PRINT @QUERY_STRING

			SET @PREVIOUS_QTY_INT = 0
			IF ( LEN ( @PREVIOUS_QTY_STR ) > 0 )
			BEGIN
				SET @PREVIOUS_QTY_STR = SUBSTRING ( @PREVIOUS_QTY_STR, 0, LEN ( @PREVIOUS_QTY_STR ) )
				SET @OLD_BIN_QUANTITIES = SUBSTRING ( @OLD_BIN_QUANTITIES, 0, LEN ( @OLD_BIN_QUANTITIES ) )
				SET @PREVIOUS_QTY_QUERY = N'declare tempCursor cursor for SELECT '+@PREVIOUS_QTY_STR+','+ @OLD_BIN_QUANTITIES +',DIE_BANK_BIN_INFO_UPDATE FROM INFORMATIVE_BINNING WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID' 
				set @ParamDefinition = N'@LOT_ID INT, @WAFER_ID INT'
				execute sp_executesql @PREVIOUS_QTY_QUERY, @ParamDefinition, @lot_id = @lot_id, @WAFER_ID = @WAFER_ID

				OPEN tempCursor
				FETCH NEXT FROM tempCursor INTO @PREVIOUS_QTY_INT, @OLD_BIN_QUANTITIES, @DIE_BANK_BIN_INFO_UPDATE
				CLOSE tempCursor
				DEALLOCATE tempCursor
			END


			/*
			If the wafer's isHold is checked, RMA_LIMT_FLAG AND MRB_LIMT_FLAG ARE SET TO 1 AND 0, there by restricting to create a assembly release for this wafer
			*/
			IF( @HOLD = 0 )
			BEGIN
				SET @RMA_LIMIT = 1
				SET @MRB_LIMIT = 0
				--Deleting the Wafer From Sort Bin Release Table, as this Wafer is on Hold
				DELETE FROM SORT_BIN_RELEASE_TABLE WHERE Lot_ID = @LOT_ID AND Wafer_ID = @WAFER_ID
			END
			ELSE
			BEGIN
				SELECT @MRB_LIMIT_VALUE=PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY LIKE 'MRB Limit' AND PROCESSSTEP_ID = @PROCESS_STEP_ID
				SELECT @RMA_LIMIT_VALUE=PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY LIKE 'RMA Limit' AND PROCESSSTEP_ID = @PROCESS_STEP_ID
				IF( @GOOD_DICE <= @RMA_LIMIT_VALUE )
				BEGIN
					SET @RMA_LIMIT = 0

					--Deleting the Wafer From Sort Bin Release Table, as this Wafer is on Hold
					DELETE FROM SORT_BIN_RELEASE_TABLE WHERE Lot_ID = @LOT_ID AND Wafer_ID = @WAFER_ID
				END
				IF( @GOOD_DICE > @RMA_LIMIT_VALUE AND @GOOD_DICE <= @MRB_LIMIT_VALUE )
				BEGIN
					SET @MRB_LIMIT = 0

					--If Wafer is not on Hold and Previouly it released explictly through MRB Release.
					IF ( @HOLD = 1  AND ( @OLD_MRB_LIMIT_STATUS = 1 AND @OLD_RMA_LIMIT_STATUS = 1 AND @PREVIOUS_QTY_INT = @GOOD_DICE ) )
					BEGIN
						SET @MRB_LIMIT = 1
						SET @RMA_LIMIT = 1
					END

					--Deleting the Wafer From Sort Bin Release Table, as this Wafer is on Hold
					DELETE FROM SORT_BIN_RELEASE_TABLE WHERE Lot_ID = @LOT_ID AND Wafer_ID = @WAFER_ID
				END
			END
			IF( (@OLD_BIN_QUANTITIES <> @BIN_QUANTITIES_COPY) OR (@OLD_MRB_LIMIT_STATUS <> @MRB_LIMIT) OR (@OLD_RMA_LIMIT_STATUS  <> @RMA_LIMIT) )
			BEGIN
				PRINT 'CHK2'
				SET @DIE_BANK_BIN_INFO_UPDATE = 0
			END
			SET @PARAM_STRING = '@GOOD_DICE INT, @YIELD FLOAT, @BIN_REJECTED INT, @MRB_LIMIT INT, @RMA_LIMIT INT, @DIE_BANK_BIN_INFO_UPDATE BIT, @LOT_ID INT, @WAFER_ID INT'
			EXECUTE SP_EXECUTESQL @QUERY_STRING, @PARAM_STRING, @GOOD_DICE, @YIELD, @BIN_REJECTED, @MRB_LIMIT, @RMA_LIMIT, @DIE_BANK_BIN_INFO_UPDATE, @LOT_ID, @WAFER_ID
		END
		IF( ( (SELECT COUNT(*) FROM ENGINEERING_LOTS  WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID) > 0 ))
		BEGIN
			UPDATE INFORMATIVE_BINNING SET RMA_LIMIT = 0 WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
		END	
		if( (SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE ASLT, ASSM_RELEASE_TABLE ART WHERE ART.ASSEMBLY_RELEASE_ID = ASLT.ASSEMBLY_RELEASE_ID AND ART.STATUS <> 'CANCELLED' AND ASLT.LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID) > 0 )
		BEGIN
			--@BIN_INFO will have all the bins of the wafer released to Assembly delimited by '&'
		    	SET @BIN_INFO = DBO.fn_get_bins_used_for_assembly_release(@LOT_ID, @WAFER_ID)
			SET @IB_BIN_QUANTITIES = ''
			SET @LOOP_VAR = 1
			/*
			If the wafer is put on HOLD, set MRB_LIMIT to 0 and RMA_LIMIT to 1 and set the available quantities 
			of bins not released to assembly to 0 in sort_bin_release_table
			else set the MRB_LIMIT and RMA_LIMIT to 1 and add back the bin quantity to available bin quantity for all the 
			bins not released to Assembly
			*/
			IF( @HOLD = 0 )
			BEGIN
				UPDATE INFORMATIVE_BINNING SET MRB_LIMIT  = 0, RMA_LIMIT = 1, DIE_BANK_BIN_INFO_UPDATE = 0  WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
				WHILE( @LOOP_VAR <= @GOOD_BINS_COUNT )
				BEGIN
					IF( CHARINDEX( '&'+CAST(@LOOP_VAR AS VARCHAR)+'&', @BIN_INFO ) = 0 )
					BEGIN
						SET @IB_BIN_QUANTITIES = @IB_BIN_QUANTITIES + 'BIN_'+CAST(@LOOP_VAR AS VARCHAR)+'_BALANCE_QTY = 0,'
						PRINT @IB_BIN_QUANTITIES
					END				
					SET @LOOP_VAR = @LOOP_VAR + 1
				END
			END
			ELSE
			BEGIN
				UPDATE INFORMATIVE_BINNING SET MRB_LIMIT  = 1, RMA_LIMIT = 1 WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
				WHILE( @LOOP_VAR <= @GOOD_BINS_COUNT )
				BEGIN
					IF( CHARINDEX( '&'+CAST(@LOOP_VAR AS VARCHAR)+'&', @BIN_INFO ) = 0 )
					BEGIN
						SET @IB_BIN_QUANTITIES = @IB_BIN_QUANTITIES + 'BIN_'+CAST(@LOOP_VAR AS VARCHAR)+'_BALANCE_QTY = BIN_'+CAST(@LOOP_VAR AS VARCHAR)+','
						PRINT @IB_BIN_QUANTITIES
					END				
					SET @LOOP_VAR = @LOOP_VAR + 1
				END
			END
			SET @IB_BIN_QUANTITIES = SUBSTRING(@IB_BIN_QUANTITIES, 0, LEN(@IB_BIN_QUANTITIES))
			IF( LEN(@IB_BIN_QUANTITIES ) > 0 )
			BEGIN
				SET @SQL_QUERY = 'UPDATE SORT_BIN_RELEASE_TABLE SET '+@IB_BIN_QUANTITIES+' WHERE LOT_ID = '+CAST(@LOT_ID AS NVARCHAR)+' AND WAFER_ID = '+CAST(@WAFER_ID AS NVARCHAR)
				EXECUTE SP_EXECUTESQL @SQL_QUERY
			END


		END
	END
	ELSE
	BEGIN

		--Wafer Id not exists in the INFORMATIVE_BINNING, Insert the Wafer Id into INFORMATIVE_BINNING Table.
		SET @QUERY_STRING = 'INSERT INTO INFORMATIVE_BINNING ( LOT_ID, WAFER_ID, GOOD_DICE, YIELD, BIN_REJECTED, MRB_LIMIT, RMA_LIMIT,DIE_BANK_BIN_INFO_UPDATE, '
		SET @TMP_IDX = 1
		SET @BINS_QUANTITY_QUERY_STRING = ''
		SET @BINS_QUANTITY_FOR_INSERT = ''
		SET @GOOD_DICE = 0
		SET @MRB_LIMIT = 1
		SET @RMA_LIMIT = 1
		SET @DIE_BANK_BIN_INFO_UPDATE = 0
	
		WHILE( CHARINDEX( ';', @BIN_QUANTITIES, 0 ) > 0 )
		BEGIN
			SET @CURRENT_BIN_VALUE = SUBSTRING( @BIN_QUANTITIES, 0, CHARINDEX( ';', @BIN_QUANTITIES, 0 ))
			SET @CURRENT_BIN_VALUE = LTRIM(RTRIM( @CURRENT_BIN_VALUE))
			SET @GOOD_DICE = @GOOD_DICE + CAST( @CURRENT_BIN_VALUE AS INT )
			SET @BINS_QUANTITY_QUERY_STRING = @BINS_QUANTITY_QUERY_STRING + 'Bin_' + CONVERT(VARCHAR,@TMP_IDX)+','
			SET @BINS_QUANTITY_FOR_INSERT = @BINS_QUANTITY_FOR_INSERT + @CURRENT_BIN_VALUE +','
			SET @BIN_QUANTITIES = SUBSTRING( @BIN_QUANTITIES, CHARINDEX(';', @BIN_QUANTITIES, 0)+1, LEN(@BIN_QUANTITIES))
			SET @TMP_IDX = @TMP_IDX + 1
		END
		SET @BINS_QUANTITY_QUERY_STRING = SUBSTRING(@BINS_QUANTITY_QUERY_STRING,0, LEN(@BINS_QUANTITY_QUERY_STRING))
		SET @BINS_QUANTITY_FOR_INSERT = SUBSTRING(@BINS_QUANTITY_FOR_INSERT,0, LEN(@BINS_QUANTITY_FOR_INSERT))
		SET @QUERY_STRING = @QUERY_STRING + @BINS_QUANTITY_QUERY_STRING +') VALUES ( @LOT_ID, @WAFER_ID, @GOOD_DICE, @YIELD, @BIN_REJECTED, @MRB_LIMIT, @RMA_LIMIT, @DIE_BANK_BIN_INFO_UPDATE, ' + @BINS_QUANTITY_FOR_INSERT + ')'
		--PRINT @QUERY_STRING
		/*
		If the wafer's isHold is checked, MRB_LIMT_FLAG AND RMA_LIMT_FLAG ARE SET TO 0 AND 1, there by restricting to create a assembly release for this wafer
		*/
		IF( @HOLD = 0 )
		BEGIN
			SET @RMA_LIMIT = 1
			SET @MRB_LIMIT = 0
		END
		ELSE
		BEGIN
			SELECT @MRB_LIMIT_VALUE=PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY LIKE 'MRB Limit' AND PROCESSSTEP_ID = @PROCESS_STEP_ID
			SELECT @RMA_LIMIT_VALUE=PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY LIKE 'RMA Limit' AND PROCESSSTEP_ID = @PROCESS_STEP_ID
			IF( @GOOD_DICE <= @RMA_LIMIT_VALUE )
			BEGIN
				SET @RMA_LIMIT = 0
			END
			IF( @GOOD_DICE > @RMA_LIMIT_VALUE AND @GOOD_DICE <= @MRB_LIMIT_VALUE )
			BEGIN
				SET @MRB_LIMIT = 0
			END
		END
		SET @PARAM_STRING = '@GOOD_DICE INT, @YIELD FLOAT, @BIN_REJECTED INT, @MRB_LIMIT INT, @RMA_LIMIT INT, @DIE_BANK_BIN_INFO_UPDATE BIT, @LOT_ID INT, @WAFER_ID INT'
		EXECUTE SP_EXECUTESQL @QUERY_STRING, @PARAM_STRING, @GOOD_DICE, @YIELD, @BIN_REJECTED, @MRB_LIMIT, @RMA_LIMIT, @DIE_BANK_BIN_INFO_UPDATE, @LOT_ID, @WAFER_ID
	END

END